﻿namespace Sidvall.Data.Sqlite
{
    public class SqlProvider : Sidvall.Data.SqlProvider
    {
        #region Public Members

        #region NameFormat

        public override string NameFormat
        {
            get
            {
                return "\"{0}\"";
            }
        }

        #endregion

        #region GetParameterName

        public override string GetParameterName(int? index)
        {
            if (index == null)
                return "$f";
            return "$f" + index.Value.ToString(System.Globalization.CultureInfo.InvariantCulture);
        }

        #endregion
        #region GetPropertyValue

        public override object GetPropertyValue(object fieldValue, DataType valueDataType)
        {
            switch (valueDataType)
            {
                case DataType.DateTime:
                    var value = fieldValue as System.DateTime?;
                    if (value != null)
                        return value.Value.ToString("yyyy-MM-dd HH:mm:ss.fff");
                    else
                        return null;
                default:
                    return fieldValue;
            }
        }

        #endregion

        #region AddWildCardParameter

        public override void AddWildCardParameter(SqlQuery query, ComparisonOperator comparisonOperator, string value)
        {
            if (string.IsNullOrWhiteSpace(value))
                return;
            base.AddWildCardParameter(query, comparisonOperator, value.ToUpperInvariant());
        }

        #endregion
        #region InitializeSqlComparison

        public override void InitializeSqlComparison(SqlQuery query, string expression, string value, ComparisonOperator comparisonOperator, object parameterValue)
        {
            if (query == null)
                return;
            if (string.IsNullOrWhiteSpace(expression))
                return;
            if (string.IsNullOrWhiteSpace(value))
                return;
            var originalValue = value;
            if (parameterValue != null)
                value = GetParameterName(query.NextParameterIndex);
            if (value.Equals("null", System.StringComparison.OrdinalIgnoreCase))
            {
                switch (comparisonOperator)
                {
                    case ComparisonOperator.BitwiseNotEqual:
                    case ComparisonOperator.NotEqual:
                    case ComparisonOperator.NotLike:
                    case ComparisonOperator.NotIn:
                        query.Sql.Append(expression + " IS NOT NULL");
                        break;
                    default:
                        query.Sql.Append(expression + " IS NULL");
                        break;
                }
            }
            else
            {
                switch (comparisonOperator)
                {
                    case ComparisonOperator.BitwiseEqual:
                        query.Sql.Append(expression + " & " + value + " == " + value);
                        break;
                    case ComparisonOperator.BitwiseNotEqual:
                        query.Sql.Append(expression + " & " + value + " == 0");
                        break;
                    case ComparisonOperator.NotEqual:
                        query.Sql.Append(expression + " <> " + value);
                        break;
                    case ComparisonOperator.LessThan:
                        query.Sql.Append(expression + " < " + value);
                        break;
                    case ComparisonOperator.LessThanOrEqual:
                        query.Sql.Append(expression + " <= " + value);
                        break;
                    case ComparisonOperator.GreaterThan:
                        query.Sql.Append(expression + " > " + value);
                        break;
                    case ComparisonOperator.GreaterThanOrEqual:
                        query.Sql.Append(expression + " >= " + value);
                        break;
                    case ComparisonOperator.In:
                    case ComparisonOperator.Like:
                    case ComparisonOperator.EndsWith:
                    case ComparisonOperator.StartsWith:
                        query.Sql.Append("replace(replace(replace(upper(" + expression + "), ");
                        if (parameterValue != null)
                        {
                            query.AppendParameter("å");
                            query.Append(", ");
                            query.AppendParameter("Å");
                            query.Append("), ");
                            query.AppendParameter("ä");
                            query.Append(", ");
                            query.AppendParameter("Ä");
                            query.Append("), ");
                            query.AppendParameter("ö");
                            query.Append(", ");
                            query.AppendParameter("Ö");
                            query.Append(") LIKE " + GetParameterName(query.NextParameterIndex));
                            AddWildCardParameter(query, comparisonOperator, originalValue);
                            parameterValue = null;
                        }
                        break;
                    case ComparisonOperator.NotLike:
                    case ComparisonOperator.NotIn:
                        query.Sql.Append("replace(upper(" + expression + "), ");
                        if (parameterValue != null)
                        {
                            query.AppendParameter("å");
                            query.Append(", ");
                            query.AppendParameter("Å");
                            query.Append("), ");
                            query.AppendParameter("ä");
                            query.Append(", ");
                            query.AppendParameter("Ä");
                            query.Append("), ");
                            query.AppendParameter("ö");
                            query.Append(", ");
                            query.AppendParameter("Ö");
                            query.Append(") NOT LIKE " + GetParameterName(query.NextParameterIndex));
                            AddWildCardParameter(query, comparisonOperator, originalValue);
                            parameterValue = null;
                        }
                        break;
                    default:
                        query.Sql.Append(expression + " = " + value);
                        break;
                }
                if (parameterValue != null)
                {
                    query.ParameterValues.Add(parameterValue);
                }
            }
        }

        #endregion
        #region InitializeSqlDelete

        public override void InitializeSqlDelete(SqlQuery query, string tableName, SqlQuery whereQuery, RelationItemCollection relations,
            string orderBySql, int? rowStartIndex, int? rowCount)
        {
            if (query == null)
                return;
            if (string.IsNullOrWhiteSpace(tableName))
                return;

            query.Append("DELETE FROM " + GetTableName(tableName));
            if ((relations != null) && (relations.Count == 1) && (relations[0].Fields.Count == 1))
            {
                var relation = relations[0];
                var field = relation.Fields[0];
                query.Append(" WHERE " + GetFieldName(null, field.SourceFieldName) + " IN (SELECT " + GetFieldName(tableName, field.SourceFieldName));
                query.Append(" FROM " + GetTableName(tableName) + " " + GetRelationSyntax(relation.RelationType) + " " + GetTableName(relation.RelatedTableName) + " ON ");
                query.Append(GetFieldName(tableName, field.SourceFieldName) + "=" + GetFieldName(relation.RelatedTableName, field.RelatedFieldName));
                query.Append(whereQuery, " WHERE ");
                query.Append(")");
            }
            else
            {
                query.Append(whereQuery, " WHERE ");
            }
            query.Append(orderBySql, " ORDER BY ");
            InitializeSqlPaging(query, rowStartIndex, rowCount);
        }

        #endregion
        #region InitializeSqlSelect

        public override void InitializeSqlSelect(SqlQuery query, string tableName, SqlQuery whereQuery, RelationItemCollection relations,
            string orderBySql, int? rowStartIndex, int? rowCount, string fieldNames)
        {
            if (query == null)
                return;
            if (string.IsNullOrWhiteSpace(tableName))
                return;
            if (string.IsNullOrWhiteSpace(fieldNames))
                return;

            query.Append("SELECT ");
            query.Append("DISTINCT " + fieldNames + " FROM " + GetTableName(tableName) + " ");
            InitializeSqlRelations(query, relations);
            query.Append(whereQuery, " WHERE ");
            query.Append(orderBySql, " ORDER BY ");
            InitializeSqlPaging(query, rowStartIndex, rowCount);
        }

        #endregion
        #region InitializeSqlPaging

        public override void InitializeSqlPaging(SqlQuery query, int? rowStartIndex, int? rowCount)
        {
            if (query == null)
                return;
            if (rowCount > 0)
            {
                query.Append(" LIMIT " + rowCount.ToString());
                if (rowStartIndex > 0)
                    query.Append(" OFFSET " + rowStartIndex.ToString());
            }
        }

        #endregion

        #endregion
    }
}
